package busterminal.persistence.jdbc;

import busterminal.entity.Trip;
import busterminal.entity.TripImplementation;
import busterminal.entity.service.TripState;
import busterminal.exception.BusinessException;
import busterminal.exception.DatabaseException;
import busterminal.persistence.TripDAO;
import busterminal.persistence.jdbc.proxy.BusProxy;
import busterminal.persistence.jdbc.proxy.RouteProxy;
import busterminal.persistence.jdbc.proxy.TripProxy;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Реализация интерфейса Data Access Object для рейса для работы с базой данных
 */
public class JdbcTripDAO extends AbstractJdbcDAO<Trip> implements TripDAO {

    /**
     * Добавить рейс в базу данных
     * @param entity ссылка на рейс
     */
    @Override
    public void create(Trip trip) {
        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement("INSERT INTO Trip (Id, RouteId, TripState, TripDate, BusId, DriverName, ConductorName) VALUES (?, ?, ?, ?, ?, ?, ?)");
            trip.setId(getNextId());
            preparedStatement.setInt(1, trip.getId());
            preparedStatement.setInt(2, trip.getRoute().getId());
            preparedStatement.setString(3, trip.getTripState().toString());
            preparedStatement.setString(4, trip.getTripDateString());
            preparedStatement.setInt(5, trip.getBus().getId());
            preparedStatement.setString(6, trip.getDriverName());
            preparedStatement.setString(7, trip.getConductorName());
            preparedStatement.execute();
        } catch (SQLException e) {
            throw new DatabaseException("Cannot create new trip: " + trip, e);
        } finally {
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException ex) {
                System.err.println("Cannot close statement");
                ex.printStackTrace(System.err);
            }
        }
    }

    /**
     * Прочитать рейс из базы данных
     * @param id идентификатор рейса
     * @return ссылка на прочитанный рейс
     */
    @Override
    public Trip read(Integer id) {
        Connection connection = getConnection();
        Statement statement = null;
        ResultSet resultSet = null;
        Trip trip = null;
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery("SELECT Id, RouteId, TripState, TripDate, BusId, DriverName, ConductorName FROM Trip WHERE Id=" + id);
            if (resultSet.next()) {
                trip = new TripImplementation();
                trip.setId(resultSet.getInt("Id"));
                int routeId = resultSet.getInt("RouteId");
                if (routeId != 0) {
                    trip.setRoute(new RouteProxy(routeId));
                }
                trip.setTripState(TripState.valueOf(resultSet.getString("TripState").toUpperCase()));
                try {
                    trip.setTripDate(resultSet.getString("TripDate"));
                } catch (ParseException parseException) {
                    throw new DatabaseException("Cannot parse trip date=" + resultSet.getString("TripDate"), parseException);
                }
                int busId = resultSet.getInt("BusId");
                if (busId != 0) {
                    trip.setBus(new BusProxy(busId));
                }
                trip.setDriverName(resultSet.getString("DriverName"));
                trip.setConductorName(resultSet.getString("ConductorName"));
            }
        } catch (SQLException e) {
            throw new DatabaseException("Cannot read trip with Id=" + id, e);
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException ex) {
                System.err.println("Cannot close statement");
                ex.printStackTrace(System.err);
            }
        }
        if (trip == null) {
            throw new BusinessException("Trip with Id=" + id + " not found");
        }
        return trip;
    }

    /**
     * Обновить сведения о рейсе в базе данные
     * @param entity ссылка на рейс
     */
    @Override
    public void update(Trip trip) {
        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement("UPDATE Trip SET RouteId=?, TripState=?, TripDate=?, BusId=?, DriverName=?, ConductorName=? WHERE Id=?");
            preparedStatement.setInt(1, trip.getRoute().getId());
            preparedStatement.setString(2, trip.getTripState().toString());
            preparedStatement.setString(3, trip.getTripDateString());
            preparedStatement.setInt(4, trip.getBus().getId());
            preparedStatement.setString(5, trip.getDriverName());
            preparedStatement.setString(6, trip.getConductorName());
            preparedStatement.setInt(7, trip.getId());
            preparedStatement.execute();
        } catch (SQLException e) {
            throw new DatabaseException("Cannot update trip with Id=" + trip.getId(), e);
        } finally {
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException ex) {
                System.err.println("Cannot close statement");
                ex.printStackTrace(System.err);
            }
        }
    }

    /**
     * Удалить рейс из базы данных
     * @param entity ссылка на рейс
     */
    @Override
    public void delete(Trip trip) {
        delete(trip.getId());
    }

    /**
     * Удалить рейс из базы данных
     * @param id идентификатор рейса
     */
    @Override
    public void delete(Integer id) {
        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement("DELETE FROM Trip WHERE Id=?");
            preparedStatement.setInt(1, id);
            preparedStatement.execute();
        } catch (SQLException e) {
            throw new DatabaseException("Cannot delete trip with Id=" + id, e);
        } finally {
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException ex) {
                System.err.println("Cannot close statement");
                ex.printStackTrace(System.err);
            }
        }
    }

    /**
     * Получить список всех рейсов из базы данных
     * @return список рейсов
     */
    @Override
    public List<Trip> findAll() {
        Connection connection = getConnection();
        Statement statement = null;
        ResultSet resultSet = null;
        List<Trip> tripList = new ArrayList<Trip>();
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery("SELECT Id FROM Trip");
            while (resultSet.next()) {
                Trip trip = new TripProxy();
                trip.setId(resultSet.getInt("Id"));
                tripList.add(trip);
            }
        } catch (SQLException e) {
            throw new DatabaseException("Cannot read trip list", e);
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException ex) {
                System.err.println("Cannot close statement");
                ex.printStackTrace(System.err);
            }
        }
        return tripList;
    }

    /**
     * Найти рейсы, осуществляемые в заданный день
     * @param date день
     * @return список найденных рейсов
     */
    @Override
    public List<Trip> findByDate(Date date) {
        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Trip> tripList = new ArrayList<Trip>();
        try {
            preparedStatement = connection.prepareStatement("SELECT Id, RouteId, TripState, TripDate, BusId, DriverName, ConductorName FROM Trip WHERE TripDate=?");
            preparedStatement.setString(1, new SimpleDateFormat("dd.MM.yyyy").format(date));
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Trip trip = new TripImplementation();
                trip.setId(resultSet.getInt("Id"));
                int routeId = resultSet.getInt("RouteId");
                if (routeId != 0) {
                    trip.setRoute(new RouteProxy(routeId));
                }
                trip.setTripState(TripState.valueOf(resultSet.getString("TripState").toUpperCase()));
                try {
                    trip.setTripDate(resultSet.getString("TripDate"));
                } catch (ParseException parseException) {
                    throw new DatabaseException("Cannot parse trip date=" + resultSet.getString("TripDate"), parseException);
                }
                int busId = resultSet.getInt("BusId");
                if (busId != 0) {
                    trip.setBus(new BusProxy(busId));
                }
                trip.setDriverName(resultSet.getString("DriverName"));
                trip.setConductorName(resultSet.getString("ConductorName"));
                tripList.add(trip);
            }
        } catch (SQLException e) {
            throw new DatabaseException("Cannot read trip with date " + new SimpleDateFormat("dd.MM.yyyy").format(date), e);
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException ex) {
                System.err.println("Cannot close statement");
                ex.printStackTrace(System.err);
            }
        }
        return tripList;
    }
}
